<?php
	session_start(); 
	ob_start();
	
	require("./inc/config-system.inc.php");
	require("./inc/config-db.inc.php");
	require("./inc/fm.inc.php");
	require("./class/adodb/adodb.inc.php");
	
	// connection database
	$objConn = ADONewConnection(DB_DRIVER); 
	$objConn->Connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
	//$objConn->debug = TRUE;
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>AR Form Document Posting</title>
<style type="text/css">
<!--
.center {
	text-align: center;
	font-weight: normal;
	font-size: 14px;
	font-family: "Microsoft Sans Serif";
}
.right {
	text-align: right;
}
.body {
	font-family: "Microsoft Sans Serif";
	font-size: 14px;
	margin-top: 0px;
}
.input {
	font-family: "Microsoft Sans Serif";
	font-size: 14px;
}
.header_center {
	font-family: "Microsoft Sans Serif";
	font-size: 14px;
	text-decoration: underline;
	text-align: center;
	font-weight: bold;
	color: #FFF;
	background-color: #600;
}
.input_disable {
	font-family: "Microsoft Sans Serif";
	font-size: 14px;
	color: #000;
	background-color: #CCC;
}
.color_row {
	font-family: "Microsoft Sans Serif";
	font-size: 14px;
	color: #000;
	background-color:#F5F5F5;
}
.input_focus {
	font-family: "Microsoft Sans Serif";
	font-size: 14px;
	background-color: #FFC;
	border: 2px solid #F60;
}
.top {
	-moz-border-radius-bottomleft:0.5em;
	-moz-border-radius-bottomright:0.5em;
	-moz-border-radius-topleft:0.5em;
	-moz-border-radius-topright:0.5em;
	background-color: #000;
	border:2px solid #F90;
	color: #006;
	display:block;
	overflow:auto;
	padding:6px 12px;
	white-space:pre;
}

-->
</style>

<link href="./css/datepicker.css" rel="stylesheet" type="text/css" media="screen" />
<link href="./css/tablesorter.css" rel="stylesheet" type="text/css" media="screen" />
<link href="./css/tablehover.css" rel="stylesheet" type="text/css" media="screen" />
<link href="./css/slidepanel.css" rel="stylesheet" type="text/css" media="screen" />
<link href="./css/autocomplete.css" rel="stylesheet" type="text/css" media="screen" />

<script type="text/javascript" src="./js/jquery.inc.js"></script>
<script type="text/javascript" src="./js/form.inc.js"></script>
<script type="text/javascript" src="./js/datepicker.inc.js"></script>
<script type="text/javascript" src="./js/tablesorter.inc.js"></script>
<script type="text/javascript" src="./js/tablehover.inc.js"></script>
<script type="text/javascript" src="./js/livequery.inc.js"></script>
<script type="text/javascript" src="./js/autocomplete.inc.js"></script>
<script type="text/javascript" src="./js/floatingbox.inc.js"></script>

<?php require("./gl.inc.js.php"); ?>
</head>
<body class="body">

<div id="mainPanel">
    <div id="panel"><?php require("./menu.inc.php"); ?></div> 
    <p class="slide"><a href="#" class="btn-slide">Main Menu</a></p> 
</div>
 
<div class="top" id="top"><a href="#inputbox">Go to TOP</a></div>

<br /><br />
 
<?php
	// get filter from user input
	if (isset($_POST["TXTgl_account"])) {
		$gl_account = $_POST["TXTgl_account"];
	} else {
		$gl_account = "";
	}
	
	if (isset($_POST["TXTdoc_date_from"])) {
		$doc_date_from = $_POST["TXTdoc_date_from"];
	} else {
		$doc_date_from = "";
	}
	
	if (isset($_POST["TXTdoc_date_to"])) {
		$doc_date_to = $_POST["TXTdoc_date_to"];
	} else {
		$doc_date_to = "";
	}
?>
<form id="gl_sum_display" method="post" action="./gl_sum_all_display.php">
    <table width="100%" border="0" cellpadding="2" cellspacing="2" class="body">
        <tr class="header_center">
            <td><a name="inputbox">GL Seletion Filter</a></td>
        </tr>
        <tr>
            <td class="left">&nbsp;</td>
        </tr>
        <tr>
            <td>GL Account :<br />
			<input name="TXTgl_account" type="text" class="input" id="TXTgl_account" value="<?php echo $gl_account; ?>" /></td>
		</tr>
        <tr>
            <td>Document Date :<br />
            <input name="TXTdoc_date_from" type="text" class="input" id="TXTdoc_date_from" value="<?php echo $doc_date_from; ?>" readonly="readonly" />
            To&nbsp;
            <input name="TXTdoc_date_to" type="text" class="input" id="TXTdoc_date_to" value="<?php echo $doc_date_to; ?>" readonly="readonly" /></td>
		</tr>
        <tr>
            <td><input type="submit" name="BTTexecute" id="BTTexecute" value="Execute" class="input" />
                <input type="reset" name="BTTreset" id="BTTreset" value="Reset" class="input" />
            </td>
        </tr>
</table>
</form>

<?php if (isset($_POST["BTTexecute"])) { ?>

	<?php
		// replace * to % for use in SQL
		if ($gl_account == "") {
			$gl_account = "%";
		} else {
			$gl_account = str_replace("*", "%", $gl_account);
		}

		if ($doc_date_from == "") {
			$doc_date_from = "0000-00-00";
		} else {
			$doc_date_from = str_replace("*", "%", $doc_date_from);
		}
		
		if ($doc_date_to == "") {
			$doc_date_to = "9999-12-31";
		} else {
			$doc_date_to = str_replace("*", "%", $doc_date_to);
		}

		// keep AR data into result array
		$result_data = array();
		$gl_account_temp = "";
		
		$sqlCommand = "SELECT * FROM tbl_ar AS a INNER JOIN tbl_gl AS b ON a.ar_account = b.gl_account WHERE a.ar_account LIKE '" . $gl_account . "' AND (a.ar_docdate BETWEEN '" . $doc_date_from . "' AND '" . $doc_date_to . "') ORDER BY a.ar_account";  
		$recordset = $objConn->Execute($sqlCommand);
		
		while (!$recordset->EOF) {
			$result_data_temp["gl_account"] = $recordset->fields["ar_account"];
			$result_data_temp["pk"] = $recordset->fields["ar_postkey"];
			$result_data_temp["gl_description"] = $recordset->fields["gl_description"];
			$result_data_temp["docno"] = "AR" . str_pad($recordset->fields["ar_docno"], 8, 0, STR_PAD_LEFT);
			$result_data_temp["docdate"] = $recordset->fields["ar_docdate"];
			$result_data_temp["description"] = $recordset->fields["ar_description"];
			$result_data_temp["quantity"] = $recordset->fields["ar_quantity"];
			$result_data_temp["price_unit"] = $recordset->fields["ar_price_unit"];
			$result_data_temp["amount"] = $recordset->fields["ar_amount"];
			
			array_push($result_data, $result_data_temp);
			$recordset->MoveNext();
		}
		unset($result_data_temp); // clear array
		
		// keep AP data into result array
		$sqlCommand = "SELECT * FROM tbl_ap AS a INNER JOIN tbl_gl AS b ON a.ap_account = b.gl_account WHERE a.ap_account LIKE '" . $gl_account . "' AND (a.ap_docdate BETWEEN '" . $doc_date_from . "' AND '" . $doc_date_to . "') ORDER BY a.ap_account";  
		$recordset = $objConn->Execute($sqlCommand);
		
		while (!$recordset->EOF) {
			$result_data_temp["gl_account"] = $recordset->fields["ap_account"];
			$result_data_temp["pk"] = $recordset->fields["ap_postkey"];
			$result_data_temp["gl_description"] = $recordset->fields["gl_description"];
			$result_data_temp["docno"] = "AP" . str_pad($recordset->fields["ap_docno"], 8, 0, STR_PAD_LEFT);
			$result_data_temp["docdate"] = $recordset->fields["ap_docdate"];
			$result_data_temp["description"] = $recordset->fields["ap_description"];
			$result_data_temp["quantity"] = $recordset->fields["ap_quantity"];
			$result_data_temp["price_unit"] = $recordset->fields["ap_price_unit"];
			$result_data_temp["amount"] = $recordset->fields["ap_amount"];
			
			array_push($result_data, $result_data_temp);
			$recordset->MoveNext();
		}
		unset($result_data_temp); // clear array
		
		// keep ADJUST data into result array
		$sqlCommand = "SELECT * FROM tbl_adj AS a INNER JOIN tbl_gl AS b ON a.adj_account = b.gl_account WHERE a.adj_account LIKE '" . $gl_account . "' AND (a.adj_docdate BETWEEN '" . $doc_date_from . "' AND '" . $doc_date_to . "') ORDER BY a.adj_account";  
		$recordset = $objConn->Execute($sqlCommand);
		
		while (!$recordset->EOF) {
			$result_data_temp["gl_account"] = $recordset->fields["adj_account"];
			$result_data_temp["pk"] = $recordset->fields["adj_postkey"];
			$result_data_temp["gl_description"] = $recordset->fields["gl_description"];
			$result_data_temp["docno"] = "AD" . str_pad($recordset->fields["adj_docno"], 8, 0, STR_PAD_LEFT);
			$result_data_temp["docdate"] = $recordset->fields["adj_docdate"];
			$result_data_temp["description"] = $recordset->fields["adj_description"];
			$result_data_temp["quantity"] = $recordset->fields["adj_quantity"];
			$result_data_temp["price_unit"] = $recordset->fields["adj_price_unit"];
			$result_data_temp["amount"] = $recordset->fields["adj_amount"];
			
			array_push($result_data, $result_data_temp);
			$recordset->MoveNext();
		}
		unset($result_data_temp); // clear array
		
		// sort result array by value
		asort($result_data);
	?>
    
 	<table width="100%">
        <tr class="header_center">
            <td colspan="4">GL Display Result (Break by Account)</td>
        </tr>
    </table>
 
<table id="mainTableSum" width="100%"> 
	<tr><td>
   
    <table id="myTable" name="nameTable" class="tablesorter" width="100%" border="0" cellpadding="2" cellspacing="2">
		<thead class="input_disable"><tr>
            <th width="15%">Account</th>
            <th width="46%">Account Description</th>
            <th width="18%">Amount Debit</th>
            <th width="21%">Amount Credit</th>
        </tr></thead>
        
        <tbody>
<?php 	
		// group same gl account to one line
		$sum_sub_total = 0.00;
		$sum_debit = 0.00;
		$sum_credit = 0.00;
		$sign_string_amount = "";
		$i = 1;
		
		while (list($key, $value) = each($result_data)) { // !!! do not use foreach() !!!
	
			if ($value["gl_account"] != $gl_account_temp) { 
				$gl_account_temp = $value["gl_account"];
				$sum_sub_total = 0.00;
			}
			
			// GL 1 or 2 or 3 or 4 or 5
			if (substr($value["gl_account"], 0, 1) == "1" || 
				substr($value["gl_account"], 0, 1) == "2" ||
				substr($value["gl_account"], 0, 1) == "3" ||
				substr($value["gl_account"], 0, 1) == "4" ||
				substr($value["gl_account"], 0, 1) == "5") {
				
				if ($value["pk"] == "01") {
					$sign_string_amount = $value["amount"] = $value["amount"];
				} elseif ($value["pk"] == "02") {
					$value["amount"] = $value["amount"] * -1;
					$sign_string_amount = shiftLeftAndRoate($value["amount"]); // show sign minus SAP format
				}
			}
						
			$sum_sub_total = $sum_sub_total + $value["amount"];

			// read next record array for check end of gl account
  			$next_record = current($result_data); // ห้ามใช้ next() เพราะว่า cursor เลื่อนมาที่แถวต่อไปแล้วใช้ current() ดึงออกมาได้เลย
			
  			if ($next_record["gl_account"] != $value["gl_account"]) {
				
            	if ($sum_sub_total < 0) {
					
					$sum_credit = $sum_credit + $sum_sub_total; // grand credit
					
					$sign_string_amount = shiftLeftAndRoate($sum_sub_total); // show sign minus SAP format

					$debit = "0.00";
					$credit = "<b><font color=red>" . $sign_string_amount . "</font></b>";
					
			  	} elseif ($sum_sub_total == 0) {
					
				 	$sign_string_amount = "0.00"; 
					
					$debit = "0.00";
					$credit = "0.00";
					
			  	} else { // > 0

					$sum_debit = $sum_debit + $sum_sub_total; // grand debit

				 	$sign_string_amount = number_format($sum_sub_total, 2); 
					
					$debit = "<b><font color=green>" . $sign_string_amount . "</font></b>";
					$credit = "0.00";
					
				}
				
/*				if ($i % 2 == 0) {
					$classrow = "color_row";	
				} else {
					$classrow = "";	
				}
				
				$i++;
*/				
?>		
				<tr>
					<td><?php echo $value["gl_account"]; ?></td>
					<td><?php echo $value["gl_description"]; ?></td>
					<td><?php echo $debit; ?></td>
					<td><?php echo $credit; ?></td>
				</tr>
        
				
<?php
			}
			
		}
  	?>

<?php } // if isset($_POST["BTTexecute"] ?>

        </tbody>
</table>

<?php
	if (isset($sum_debit) && isset($sum_credit)) {
		$sign_string_sum_debit = number_format($sum_debit, 2);
		$sign_string_sum_credit = shiftLeftAndRoate($sum_credit); // show sign minus SAP format
	} else { // first run
		$sign_string_sum_debit = "";
		$sign_string_sum_credit = "";
	}
?>

<table width="100%">
    <tr>
        <td width="15%">&nbsp;</td>
        <td width="46%">&nbsp;</td>
        <td width="18%">&nbsp;</td>
        <td width="21%">&nbsp;</td>
    </tr>
    <tr>
        <td>&nbsp;</td>
        <td>&nbsp;</td>
        <td><?php echo "<b>" . $sign_string_sum_debit . "</b>"; ?></td>
        <td><?php echo "<b>" . $sign_string_sum_credit . "</b>"; ?></td>
    </tr>
</table>

</td></tr><table>

<?php if (isset($_POST["BTTexecute"])) { ?>
    <form action="./export_sumgl_2_excel.php" method="post">
    <table width="100%" border="0" cellpadding="2" cellspacing="2">
        <tr>
            <td width="15%">
                <input name="BTTexport" type="submit" class="input" id="BTTexport" value="Export to EXCEL" />
                <input name="HIDdata2excel" type="hidden" id="HIDdata2excel" />
            </td>
            <td width="46%">&nbsp;</td>
            <td width="18%">&nbsp;</td>
            <td width="21%">&nbsp;</td>
        </tr>
    </table> 
    </form>
<?php } ?>
       
</body>
</html>
<?php ob_end_flush(); ?>
